﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using IBM.Data.DB2;
using System.Collections.Specialized;
using CourseManager.Domain.Entities;

namespace CourseManager.Domain.Repositories
{
    public class StudentRepository
    {
        DatabaseTransac DatabaseTans;
        public Student GetStudent(int pId)
        {
            Student Student = new Student();
            string Sql = "Select id,Name,LastName,Telephone,Cellphone,Email,Address "
                          + "from {0}.Student where id = {1};";
            Sql = string.Format(Sql, Properties.Settings.Default.Schema, pId);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Student
                          {
                              Id = Convert.ToInt32(dr[0]),
                              LastName = dr[2].ToString(),
                              Telephone = dr[3].ToString(),
                              Cellphone = dr[4].ToString(),
                              Email = dr[5].ToString(),
                              Address = dr[6].ToString(),
                          }).OrderBy(i => i.Id);

            return Result.First();
        }

        public IEnumerable<Student> GetStudentByInterest(string pInterest)
        {
            Student Student = new Student();
            string Sql = "select s.id,s.Name,s.LastName,s.Telephone,s.Cellphone,s.Email,s.Address "
                        + "from ADMINISTRATOR.Student s,ADMINISTRATOR.Interest i "
                        + "where s.id = i.Idstudent and  i.Interest like '%{1}%'; ";
            Sql = string.Format(Sql, Properties.Settings.Default.Schema, pInterest);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Student
                          {
                              Id = Convert.ToInt32(dr[0]),
                              Name = dr[1].ToString(),
                              LastName = dr[2].ToString(),
                              Telephone = dr[3].ToString(),
                              Cellphone = dr[4].ToString(),
                              Email = dr[5].ToString(),
                              Address = dr[6].ToString(),
                          }).OrderBy(i => i.Name);

            return Result.AsEnumerable();

        }

        public IEnumerable<Student> GetStudentByPosition(string pPosition)
        {
            Student Student = new Student();

            string Sql = "select id,Name,LastName,Telephone,Cellphone,Email,Address "
                        + "from {0}.Student where position like '%{1}%'; ";
            Sql = string.Format(Sql, Properties.Settings.Default.Schema, pPosition);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Student
                          {
                              Id = Convert.ToInt32(dr[0]),
                              LastName = dr[2].ToString(),
                              Telephone = dr[3].ToString(),
                              Cellphone = dr[4].ToString(),
                              Email = dr[5].ToString(),
                              Address = dr[6].ToString(),
                          }).OrderBy(i => i.Name);

            return Result.AsEnumerable();

        }


        public IEnumerable<Student> GetStudent(string pName)
        {
            Student Student = new Student();
            string Sql = "select id,Name,LastName,Telephone,Cellphone,Email,Address "
                        + "from {0}.Student where Name like '%{1}%'; ";
            Sql = string.Format(Sql, Properties.Settings.Default.Schema, pName);

            DatabaseTans = new DatabaseTransac();
            DataTable DT = DatabaseTans.executeSelect(Sql);

            var Result = (from dr in DT.AsEnumerable()
                          select new Student
                          {
                              Id = Convert.ToInt32(dr[0]),
                              LastName = dr[2].ToString(),
                              Telephone = dr[3].ToString(),
                              Cellphone = dr[4].ToString(),
                              Email = dr[5].ToString(),
                              Address = dr[6].ToString(),
                          }).OrderBy(i => i.Id);

            return Result.AsEnumerable();

        }


        public void AddStudent(Student pStudent)
        {
            int StudentId;
            NameValueCollection ValuesToInsert = new NameValueCollection();

            ValuesToInsert.Add("Name", "'" + pStudent.Name.ToString() + "'");
            ValuesToInsert.Add("LastName", "'" + pStudent.LastName + "'");
            ValuesToInsert.Add("Telephone", "'" + pStudent.Telephone.ToString() + "'");
            ValuesToInsert.Add("Cellphone", "'" + pStudent.Cellphone.ToString() + "'");
            ValuesToInsert.Add("Email", "'" + pStudent.Email.ToString().ToUpper() + "'");
            ValuesToInsert.Add("Address", "'" + pStudent.Address.ToString().ToUpper() + "'");
            ValuesToInsert.Add("Position", "'" + pStudent.Position.ToString().ToUpper() + "'");

            DatabaseTans = new DatabaseTransac();
            DatabaseTans.executeInsert(Properties.Settings.Default.Schema + ".Student", ValuesToInsert);
            StudentId = SelectLastId();
            AddInterest(StudentId, pStudent.Insterest);
        }

        public void AddInterest(int StudenId, string pInterest)
        {
            NameValueCollection ValuesToInsert = new NameValueCollection();

            ValuesToInsert.Add("IdStudent", StudenId.ToString());
            ValuesToInsert.Add("Interest", "'" + pInterest + "'");
            DatabaseTans = new DatabaseTransac();
            DatabaseTans.executeInsert(Properties.Settings.Default.Schema + ".Interest", ValuesToInsert);

        }

        public void UpdateStudent(Student pStudent)
        {
            NameValueCollection ValuesToInsert = new NameValueCollection();

            ValuesToInsert.Add("Name", "'" + pStudent.Name.ToString() + "'");
            ValuesToInsert.Add("LastName", "'" + pStudent.LastName + "'");
            ValuesToInsert.Add("Telephone", "'" + pStudent.Telephone.ToString() + "'");
            ValuesToInsert.Add("Cellphone", "'" + pStudent.Cellphone.ToString() + "'");
            ValuesToInsert.Add("Email", "'" + pStudent.Email.ToString() + "'");
            ValuesToInsert.Add("Address", "'" + pStudent.Address.ToString() + "'");

            DatabaseTans = new DatabaseTransac();
            DatabaseTans.executeUpdate(Properties.Settings.Default.Schema + ".Student", ValuesToInsert, new List<string> { "Id= " + pStudent.Id });

        }

        public void DeleteStudent(int pId)
        {
            try
            {
                DatabaseTans = new DatabaseTransac();
                DatabaseTans.executeDelete(Properties.Settings.Default.Schema + ".Student", new List<string> { "Id = " + pId });

            }
            catch (Exception)
            {

                throw;
            }

        }

        private int SelectLastId()
        {
            Student Student = new Student();
            String Sql = "Select Id from {0}.Student "
                        + "Order by id Desc limit 1;";
            Sql = string.Format(Sql, Properties.Settings.Default.Schema);
            int Result;
            DB2Connection CN = new DB2Connection(Properties.Settings.Default.ConnectionString);
            DB2Command CMD = new DB2Command();
            CMD.Connection = CN;
            CMD.CommandText = Sql;
            CN.Open();
            Result = (int) CMD.ExecuteScalar();
            CN.Close();
            return Result; 
        }
    }
}
        


